﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;

namespace DRSM
{
    class dbmanager
    {
        //=========================================================================
        # region fu xiang
        // fuxiang
        
        public static void addAffectedCity( string nameOfCity, string noOfPopulation, char lvlOfDisaster, string supportPersonnelNeeded, string shortDescOfDmg)
        {
            // Step 1. Open the connection
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "data source = localhost;" +
                                        "initial catalog = DRSMdb;" +
                                        "integrated security = true;";
                conn.Open();

                //step 2 : prepare the sql statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "insert into affected_city(city_name,city_population,level_of_disaster,support_personnel_required,short_desc) values (@city_name,@city_population,@level_of_disaster,@support_personnel_required,@short_desc)";

                //put values in place holder
               
                comm.Parameters.Add("@city_name", nameOfCity);
                comm.Parameters.Add("@city_population", noOfPopulation);
                comm.Parameters.Add("@level_of_disaster", lvlOfDisaster);
                comm.Parameters.Add("@support_personnel_required", supportPersonnelNeeded);
                comm.Parameters.Add("@short_desc", shortDescOfDmg);

                int rows = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public static void updateAffectedCity(int cityID, string nameOfCity, string noOfPopulation, char lvlOfDisaster, string supportPersonnelNeeded, string shortDescOfDmg)
        {
            // Step 1. Open the connection
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "data source = localhost;" +
                                         "initial catalog = DRSMdb;" +
                                         "integrated security = true;";
                conn.Open();

                //step 2 : prepare the sql statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update affected_city set city_name = @city_name, city_population = @city_population, level_of_disaster = @level_of_disaster, support_personnel_required = @support_personnel_required, short_desc = @short_desc where city_id = @city_id";
                //comm.CommandText = "update affected_city(city_id, city_name,city_population,level_of_disaster,support_personnel_required,short_desc) values (@city_id,@city_name,@city_population,@level_of_disaster,@support_personnel_required,@short_desc)";
                //comm.CommandText = "update affected_city(city_id,city_name,city_population,level_of_disaster,support_personnel_required,short_desc) values (@city_id,@city_name,@city_population,@level_of_disaster,@support_personnel_required,@short_desc)";
               // comm.CommandText = "update affected_city set (city_id,city_name,city_population,level_of_disaster,support_personnel_required,short_desc) values (@city_id,@city_name,@city_population,@level_of_disaster,@support_personnel_required,@short_desc)";

                comm.Parameters.Add("@city_id", cityID);
                comm.Parameters.Add("@city_name", nameOfCity);
                comm.Parameters.Add("@city_population", noOfPopulation);
                comm.Parameters.Add("@level_of_disaster", lvlOfDisaster);
                comm.Parameters.Add("@support_personnel_required", supportPersonnelNeeded);
                comm.Parameters.Add("@short_desc", shortDescOfDmg);
               

                int rows = (int)comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public static void viewCityProfile()
        {
            AllFormClass.AffectedCity.Clear();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "data source = localhost;" +
                                         "initial catalog = DRSMdb;" +
                                         "integrated security = true;";
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from affected_city";

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    AffectedCity ac = new AffectedCity((int)dr[0], dr[1].ToString(), dr[2].ToString(), dr[3].ToString()[0], dr[4].ToString(), dr[5].ToString());
                    AllFormClass.AffectedCity.Add(ac);
                }
                dr.Close();
            }

            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public static void deleteSingleCity(string nameOfCity)
        {
            // Step 1. Open the connection
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "data source = localhost;" +
                                        "initial catalog = DRSMdb;" +
                                        "integrated security = true;";
                conn.Open();

                //step 2 : prepare the sql statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "delete from affected_city where city_name = @city_name";
                comm.Parameters.Add("@city_name", nameOfCity);
                comm.Connection = conn;

                int rowdeleted = (int)comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        
        #endregion
        //==========================================================================


        //==========================================================================
        # region Shi Qi
        //ADD
        public const string DBCONNSTR = "data source = localhost;" +
                                         "initial catalog = DRSMdb;" +
                                         "integrated security = true;";
        
                
        //Get all records
        public static void GetAllPersonnel()
        {
 
            //establish the connection
            AllFormClass.SupportPersonnels.Clear();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = DBCONNSTR;

            try
            {
                //open connection
                conn.Open();
                //prepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT * FROM SUPPORT_PERSONNEL";
                comm.Connection = conn;
                //execute the SQL command
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int personnelID = Convert.ToInt32(dr["personnel_ID"]);
                    string nameOfPersonnel = dr["name_of_support_personnel"].ToString();
                    string personnelIC = dr["personnel_identification_number"].ToString();
                    string yearsOfExperience = dr["years_of_experience"].ToString();
                    string serviceProvide = dr["service_that_can_provide"].ToString();
                    string typeOfPersonnel = dr["type_of_personnel"].ToString();
                    SupportPersonnels sp = new SupportPersonnels(personnelID, nameOfPersonnel, personnelIC,
                        yearsOfExperience,serviceProvide, typeOfPersonnel);
                    AllFormClass.SupportPersonnels.Add(sp);
                   
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //Close connection
                conn.Close();
            }
        }
        
        public static void AddPersonnel(int personnelId, string nameOfPersonnel, string personnelIC, string yearsOfExperience, string serviceProvided, string typeOfPersonnel)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = DBCONNSTR;

            try
            {
                //Open Connectiin
                conn.Open();
                //prepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "INSERT INTO support_personnel(name_of_support_personnel, personnel_identification_number, years_of_experience, service_that_can_provide, type_of_personnel) values (@name_of_support_personnel, @personnel_identification_number, @years_of_experience, @service_that_can_provide, @type_of_personnel)";
                comm.Parameters.AddWithValue("@name_of_support_personnel", nameOfPersonnel);
                comm.Parameters.AddWithValue("@personnel_identification_number", personnelIC);
                comm.Parameters.AddWithValue("@years_of_experience", yearsOfExperience);
                comm.Parameters.AddWithValue("@service_that_can_provide", serviceProvided);
                comm.Parameters.AddWithValue("@type_of_personnel", typeOfPersonnel);

                comm.Connection = conn;

                //exequte the SQL command
                int rowsAdded = (int)comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //close connection
                conn.Close();
            }
        }

        //UPDATE
        public static void UpdatePersonnel(int personnelId, string nameOfPersonnel, string personnelIC, string yearsOfExperience, string serviceProvided, string typeOfPersonnel)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = DBCONNSTR;

            try
            {
                //open connection
                conn.Open();
                //prepare SQL Command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "UPDATE SUPPORT_PERSONNEL SET name_of_support_personnel = @name_of_support_personnel, personnel_identification_number = @personnel_identification_number,years_of_experience = @years_of_experience, service_that_can_provide=@service_that_can_provide, type_of_personnel =@type_of_personnel where personnel_id = @personnel_id";
                comm.Parameters.Add("@personnel_id", personnelId);
                comm.Parameters.Add("@name_of_support_personnel", nameOfPersonnel);
                comm.Parameters.Add("@personnel_identification_number", personnelIC);
                comm.Parameters.Add("@years_of_experience", yearsOfExperience);
                comm.Parameters.Add("@service_that_can_provide", serviceProvided);
                comm.Parameters.Add("@type_of_personnel", typeOfPersonnel);

                comm.Connection = conn;

                //Execute the SQL command
                int rowsAdded = (int)comm.ExecuteNonQuery();
                Console.WriteLine(rowsAdded + " rows affected.");
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //stop connection
                conn.Close();
            }
        }
        //delete personnel
        public static void DeleteSupportPersonnel(string id)
        {
            SqlConnection conn = new SqlConnection();
        try
        {
            conn.ConnectionString = DBCONNSTR;
            conn.Open();
            //prepare the sql statement
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "DELETE from support_personnel where personnel_id = @personnel_id";
            comm.Parameters.Add("@personnel_id", id);
            comm.Connection = conn;
            int rowdeleted = (int)comm.ExecuteNonQuery();
        }
            
            catch(SqlException)
            {
            
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion

        //==========================================================================


        //==========================================================================

        # region amanda
        public static void AddPhysicalAid ( string type, string itemName, string quantity, string description) 
        {
            // Step 1. Open the connection
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "data source = localhost;" +
                                        "initial catalog = DRSMdb;" +
                                        "integrated security = true;";
                conn.Open();

                // Step 2. Prepare the SQL Statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "insert into physical_aid(item_name, item_type, item_quantity, item_description) values (@item_name,@item_type,@item_quantity,@item_description)";
                // put values in place holder
                //comm.Parameters.Add("@item_id", itemID);
                comm.Parameters.Add("@item_name", itemName);
                comm.Parameters.Add("@item_type", type);
                comm.Parameters.Add("@item_quantity", quantity);
                comm.Parameters.Add("@item_description", description);
                

                int rows = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        } 

        //Get all records
        public static void GetAllAids()
        {
            //establish the connection
            AllFormClass.PhysicalAids.Clear();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = DBCONNSTR;

            try
            {
                //open connection
                conn.Open();
                //prepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT * FROM PHYSICAL_AID";
                comm.Connection = conn;
                //execute the SQL command
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int itemID = Convert.ToInt32(dr["item_id"]);
                    string itemName = dr["item_name"].ToString();
                    string type = dr["item_type"].ToString();
                    string quantity = dr["item_quantity"].ToString();
                    string description = dr["item_decription"].ToString();
                    PhysicalAids pa = new PhysicalAids(itemID, itemName, type, quantity, description);
                    AllFormClass.PhysicalAids.Add(pa);
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //Close connection
                conn.Close();
            }
        }
        public static void ViewPhysicalAid()
        {
            AllFormClass.PhysicalAids.Clear();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "data source = localhost;" +
                                        "initial catalog = DRSMdb;" +
                                        "integrated security = true;";
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from physical_aid";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    PhysicalAids pa = new PhysicalAids((int)dr[0], dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString());
                    AllFormClass.PhysicalAids.Add(pa);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();

            }
        }
        public static void deletePhysicalAid(string itemName)
        {
            // Step 1. Open the connection
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "data source = localhost;" +
                                        "initial catalog = DRSMdb;" +
                                        "integrated security = true;";
                conn.Open();
                //step 2 : prepare the sql statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "delete from physical_aid where item_name = @item_name";
                comm.Parameters.Add("@item_name", itemName);
                comm.Connection = conn;

                int rowdeleted = (int)comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();

            }
        }
        public static void UpdatePhysicalAid(int itemID, string itemName, string type, string quantity, string description)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "data source = localhost;" +
                                    "initial catalog = DRSMdb;" +
                                    "integrated security = true;";
            try
            {
                //open connection
                conn.Open();
                //prepare SQL Command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "UPDATE PHYSICAL_AID SET item_name = @item_name, item_type = @item_type ,item_quantity = @item_quantity, item_description=@item_description WHERE item_id = @item_id";
                comm.Parameters.Add("@item_id", itemID);
                comm.Parameters.Add("@item_name", itemName);
                comm.Parameters.Add("@item_type", type);
                comm.Parameters.Add("@item_quantity", quantity);
                comm.Parameters.Add("@item_description", description);

                comm.Connection = conn;
                //Execute the SQL command
                int rowsAdded = (int)comm.ExecuteNonQuery();
                Console.WriteLine(rowsAdded + " rows affected.");
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //stop connection
                conn.Close();
            }


        }



        #endregion
        //==========================================================================
    }
}

